package questionnaire.JDBC;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.util.Date;

import org.apache.log4j.Logger;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

public class JDBC {
	static Logger log = Logger.getLogger(JDBC.class);
	
	private static final String driver="com.mysql.jdbc.Driver";
	private static final String url = "jdbc:mysql://localhost:3306/student";
	private static final String dataUser = "root";
	private static final String dataPassword = "123456";
	static Connection connection;
	static Statement statement;
	static PreparedStatement ps;
	static ResultSet rs;
	static String TableName = "user";
	static String sql = "SELECT * from user where user_name= 'wangzhihua' ";
	static String sqlPrepared = "SELECT * from user where user_name= ? ";
	static String sqlSelectAll ="SELECT * FROM user";
	static String sqlInsert = "insert into user(user_name,nice,password,birthday,phone,email) values(?,?,?,?,?,?)";
	static String sqlDelete = "DELETE FROM "+TableName;
	static{
		try {
			Class.forName(getDriver());
			log.info("注册JDBC驱动程序，下一步，准备获取（打开）数据库连接对象Connection");
			 
		} catch (ClassNotFoundException e) {
				
			e.printStackTrace();
		}  
	}
	@Before
	public void getConnecton(){
		try {
			connection = DriverManager.getConnection(getUrl(), getDatauser(), getDatapassword());
			log.info("数据库连接对象Connection已打开。");
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			log.error("获取数据库连接异常。");
			e.printStackTrace();
		}
	 
		
	}
	/**
	 * 按照指定条件查询数据
	 */
	@Test
   public void selectTable(){
		
	   try {
		statement = connection.createStatement();
	 rs = 	statement.executeQuery(sql);
	 while(rs.next()){
		 String a = rs.getString(1);
		 String b = rs.getString(2);
		 String c = rs.getString(3);
		 String d = rs.getString(4);
		 log.info("ID:"+a);
		 log.info(b);
		 log.info(c);
		 log.info(d);
	 }
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
   }
	/**
	 * 按照指定条件查询数据
	 */
	@Test
	public void selectTable2(){
		log.info(Thread.currentThread().getStackTrace()[1].getMethodName()+"执行。=========================");

		try {
			ps = connection.prepareStatement(sqlPrepared);
			ps.setString(1, "wangzhihua");
			rs = 	ps.executeQuery(sql);
			while(rs.next()){
				String a = rs.getString(1);
				String b = rs.getString(2);
				String c = rs.getString(3);
				String d = rs.getString(4);
				log.info("ID:"+a);
				log.info(b);
				log.info(c);
				log.info(d);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	/**
	 *添加数据到数据库中。
	 */
	@Test
	public void insert(){
		log.info(Thread.currentThread().getStackTrace()[1].getMethodName()+"执行。=========================");
		Date date = new Date();
		String textDateTime = DateFormat.getDateTimeInstance().format(date); //格式化时间对象
		try {
			 
			ps = connection.prepareStatement(sqlInsert);
			 
			ps.setString(1, "wangzhihua");
			ps.setString(2, "王志华");
			ps.setString(3, "123");
			ps.setString(4, textDateTime);
			ps.setString(5, "15683007565");
			ps.setString(6, "wzhjbfq3@163.com");
			
		 int ResultCount = 	ps.executeUpdate();
		 
		 	log.info("添加返回值为："+ResultCount);
		 
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	/**
	 *添加数据到数据库中。
	 */
	@Test
	public void delete(){
		log.info(Thread.currentThread().getStackTrace()[1].getMethodName()+"执行。=========================");
		
		 
			try {
				ps = connection.prepareStatement(sqlDelete);
				int deleteCount = ps.executeUpdate();
				log.info("删除数据记录执行结果："+deleteCount);
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	 
	}
	/**
	 * 查询表中的全部数据。
	 */
	@Test
	public void selectAll(){
		log.info(Thread.currentThread().getStackTrace()[1].getMethodName()+"执行。");
		try {
			statement = connection.createStatement();
			rs = 	statement.executeQuery(sqlSelectAll);
			while(rs.next()){
				 String a = rs.getString(1);
				 String b = rs.getString(2);
				 String c = rs.getString(3);
				 String d = rs.getString(4);
				 log.info("ID:"+a);
				 log.info(b);
				 log.info(c);
				 log.info(d);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	
	@After
	public void close(){
	log.info("清除数据连接。");	 
			try {
				if(rs!=null)
				rs.close();
				if(statement!=null)
					statement.close();
				if(connection!=null)
					connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		
	}
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	private static String getDriver() {
		return driver;
	}
	private static String getUrl() {
		return url;
	}
	private static String getDatauser() {
		return dataUser;
	}
	private static String getDatapassword() {
		return dataPassword;
	}
	
}
